package com.samples.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Bora SAYINER
 * @since Jan 24, 2015 7:17:57 PM
 * @version 1.0
 */
public class DbHelper {

	private static final String DB_USER_NAME = "root";
	private static final String DB_PASSWORD = "Passw0rd";
	private static final String DB_URL = "jdbc:mysql://localhost:3306/peoples";

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public List<Customer> getAll() {
		List<Customer> customers = new ArrayList<Customer>();
		try (Connection connection = createConnection()) {
			try (Statement statement = connection.createStatement()) {
				final String sqlCmd = "SELECT * FROM customers";
				ResultSet resultSet = statement.executeQuery(sqlCmd);
				while (resultSet.next()) {
					customers.add(createEntity(resultSet));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customers;
	}

	public List<Customer> getCustomerByPage(int startIndex, int pageSize) {
		List<Customer> customers = new ArrayList<Customer>();
		try (Connection connection = createConnection()) {
			final String sqlCmd = "SELECT * FROM customers LIMIT ?,?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, startIndex);
				statement.setInt(2, pageSize);
				ResultSet resultSet = statement.executeQuery();
				while (resultSet.next()) {
					customers.add(createEntity(resultSet));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return customers;
	}

	public boolean insertCustomer(Customer customer) {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "INSERT INTO customers (customername,email,phone,city) VALUES (?,?,?,?)";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setString(1, customer.getName());
				statement.setString(2, customer.getEmail());
				statement.setString(3, customer.getPhone());
				statement.setString(4, customer.getCity());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
				if (result) {
					ResultSet generatedKeys = statement.getGeneratedKeys();
					if (generatedKeys.next()) {
						int id = generatedKeys.getInt(1);
						customer.setId(id);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean deleteCustomer(int id) {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "DELETE FROM customers where customerid = ?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setInt(1, id);
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	public boolean updateCustomer(Customer customer) {
		boolean result = false;
		try (Connection connection = createConnection()) {
			final String sqlCmd = "UPDATE customers SET customername=?,email=?,phone=?,city=? WHERE customerid=?";
			try (PreparedStatement statement = connection.prepareStatement(sqlCmd)) {
				statement.setString(1, customer.getName());
				statement.setString(2, customer.getEmail());
				statement.setString(3, customer.getPhone());
				statement.setString(4, customer.getCity());
				statement.setInt(5, customer.getId());
				int affectedRows = statement.executeUpdate();
				result = affectedRows > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	private Connection createConnection() throws SQLException {
		return DriverManager.getConnection(DB_URL, DB_USER_NAME, DB_PASSWORD);
	}

	private Customer createEntity(ResultSet resultSet) throws SQLException {
		Customer customer = new Customer();
		customer.setId(resultSet.getInt("customerid"));
		customer.setName(resultSet.getString("customername"));
		customer.setEmail(resultSet.getString("email"));
		customer.setPhone(resultSet.getString("phone"));
		customer.setCity(resultSet.getString("city"));
		return customer;
	}
}
